import { App } from "@/models/app";
import { RemovableRef, useAsyncState, useStorage } from "@vueuse/core";
import initSqlJs, { Statement } from "sql.js";
import { Database, SqlJsStatic } from "sql.js";
import { Ref, ref } from "vue";
import * as jszip from "jszip";
import { base64ToBytes } from "./utils";


export type TDbServices = ReturnType<typeof getServices>


export function getServices(app: App, wasm_base64: { file: any }) {
    const { isReady, sqlEngine, sqlDb, dbFileBs64Storage } = initDb(app, wasm_base64);

    function uploadDbFile(file: File) {

        let reader = new FileReader();

        reader.onload = async function (e) {
            dbFileBs64Storage.value = e.target?.result as string
            sqlDb.value = new sqlEngine.value.Database(await dbFile2bytes(dbFileBs64Storage.value))
        }

        reader.readAsText(file, 'utf8')
    }

    function queryAll(code: string) {
        let stmt: Statement | null = null


        let fields = [] as string[]
        const rows = [] as initSqlJs.ParamsObject[]

        try {
            stmt = sqlDb.value.prepare(code);
            fields = stmt.getColumnNames()

            stmt.bind({});
            while (stmt.step()) {
                const row = stmt.getAsObject()
                rows.push(row)
            }

            function* toRowsArrayHasHeader() {
                yield fields
                for (const r of rows) {
                    yield fields.map(f => r[f])
                }
            }

            return {
                fields, rows,
                toRowsArrayHasHeader
            }


        } catch (error) {
            console.trace(error)
            throw new Error(`${error};\nsql: ${code}`);
        } finally {
            stmt?.free()
        }
    }

    function runOnTransaction<TReturn>(handler: () => TReturn, type: 'COMMIT' | 'ROLLBACK' = 'COMMIT') {
        try {
            sqlDb.value.run('BEGIN')
            return handler()
        } catch (error) {
            throw error
        } finally {
            sqlDb.value.run(type)
        }
    }

    function query2tempory(tableName: string, query: string) {
        const sqlstr = `CREATE TABLE ${tableName} as ${query}`
        sqlDb.value.run(sqlstr)
        return tableName
    }

    function getTableFields(table: string) {
        const sql = `PRAGMA table_info(${table})`

        const res = queryAll(sql).rows
        return res.map(v => v['name'] as string)
    }

    return {
        isReady,
        uploadDbFile,
        queryAll,
        query2tempory,
        runOnTransaction,
        getTableFields,
    }
}










function initDb(app: App, wasm_base64: { file: any; }) {
    const wasm_buffer = Uint8Array.from(window.atob(wasm_base64['file']), c => c.charCodeAt(0)).buffer;


    const sqlEngine = ref(null as unknown as SqlJsStatic)
    const sqlDb = ref(null as unknown as Database)
    const dbFileBs64Storage = useStorage('pybi-db', null as string | null)

    const myBlob = new Blob([wasm_buffer], { type: 'application/wasm' });

    async function loadDbFile() {
        sqlEngine.value = await initSqlJs({
            locateFile: file => URL.createObjectURL(myBlob)
        });

        let fileBs64: string | null = null;

        if (app.dbLocalStorage) {
            if (!dbFileBs64Storage.value) {
                dbFileBs64Storage.value = app.dbFile;
            }

            fileBs64 = dbFileBs64Storage.value;
        } else {
            dbFileBs64Storage.value = null;
            fileBs64 = app.dbFile;
        }


        sqlDb.value = new sqlEngine.value.Database(await dbFile2bytes(fileBs64));
    }

    const { isReady } = useAsyncState(loadDbFile, null);

    return {
        isReady,
        sqlEngine,
        sqlDb,
        dbFileBs64Storage,
    };
}

/*
   'a' =>  `'a'`
   1 => 1
*/
export function value2sqlValue(value: any) {
    if (typeof value === 'string') {
        return `"${value}"`
    }
    return value
}

/*
   ['a',1] =>  [`'a'`,1]
*/
export function valuesArray2sqlArray(values: any[]) {
    return values.map(value2sqlValue)
}



export async function zipFile2Uint8Array(zipFile: Uint8Array) {
    const file = await jszip.loadAsync(zipFile)
    const key = Object.keys(file.files)[0]

    const res = file.files[key].async('uint8array')
    return res
}



export async function dbFile2bytes(fileBs64: string) {
    const zipFile = base64ToBytes(fileBs64)

    return await zipFile2Uint8Array(zipFile)
}